package com.izhaowo.recommend.contrl;

import com.izhaowo.recommend.util.PhoenixConn;
import com.izhaowo.recommend.vo.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Created by HASEE on 2020/6/11.
 */
public class PlannerMsg {

    private static final Logger logger = LoggerFactory.getLogger(PlannerMsg.class);

    public static List<PlannerUserVO> queryAllWorkers() {
        List<PlannerUserVO> list = new ArrayList<>(200);
        try {
            Connection con = PhoenixConn.getConnection();
            Statement stmt = con.createStatement();
            StringBuilder sqlline = new StringBuilder();
//            sqlline.append("select w.id,w.name,w.user_id,to_number(w.daily_limit) daily_limit ,u.avator,u.msisdn ").
//                    append("from v3_tb_worker w inner  join v3_tb_user u on w.user_id = u.id ").
//                    append("where status = '0' and vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580'");
            sqlline.append("select id,name,user_id,daily_limit from v3_tb_worker where status = '0' and vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580' ").
                    append(" and  daily_limit is not null and to_number( daily_limit) >0 ");
            logger.info("____ query sql queryAllWorkers is " + sqlline.toString());
            ResultSet rs = stmt.executeQuery(sqlline.toString());

            while (rs.next()) {
                PlannerUserVO vo = new PlannerUserVO();
                vo.setId(rs.getString(1));
                vo.setName(rs.getString(2));
                vo.setUserId(rs.getString(3));
                vo.setDailyLimit(Integer.valueOf(rs.getString(4)));
//                vo.setAvator(rs.getString(5));
//                vo.setMsisdn(rs.getString(6));
                list.add(vo);
            }
            con.close();
        } catch (Exception e) {
            logger.error("___" + e.getMessage());
        }
        return list;
    }

    public static List<UserVO> queryAllUsers() {
        List<UserVO> list = new ArrayList<>(200);
        try {
            Connection con = PhoenixConn.getConnection();
            Statement stmt = con.createStatement();
            String sql2 = "select id,avator,msisdn from tb_user";
            ResultSet rs = stmt.executeQuery(sql2);
            while (rs.next()) {
                UserVO vo = new UserVO();
                vo.setId(rs.getString("id"));
                vo.setAvator(rs.getString("avator"));
                vo.setMsisdn(rs.getString("msisdn"));
                list.add(vo);
            }
            con.close();
        } catch (Exception e) {
            logger.error("___" + e.getMessage());
        }
        return list;
    }

    public static List<WorkerServiceVO> queryWorkerService() {
        List<WorkerServiceVO> list = new ArrayList<>(200);
        try {
            Connection con = PhoenixConn.getConnection();
            Statement stmt = con.createStatement();
            StringBuilder sqlline = new StringBuilder();
            sqlline.append("select s.id,s.worker_id, s.service_id,s.memo,s.display_amount,s.settlement_amount,s.status ,a.activity_amount ").
                    append(" from v3_tb_worker_service s left join v3_tb_worker_activity a on s.id = a.attach_id").append(
                    "  inner join v3_tb_worker w on s.worker_id = w.id where w.status = '0' and w.vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580' and ").append(
                    " w.daily_limit is not null and to_number( w.daily_limit) >0 and   s.status = '0' and a.status = '0' and s.service_id is not null");
            logger.info("____ query sql queryWorkerService is " + sqlline.toString());
            ResultSet rs = stmt.executeQuery(sqlline.toString());
            while (rs.next()) {
                WorkerServiceVO vo = new WorkerServiceVO();
                vo.setId(rs.getString(1));
                vo.setWorkerId(rs.getString(2));
                vo.setServiceId(rs.getString(3));
                vo.setMemo(rs.getString(4));
                vo.setStatus(Integer.valueOf(rs.getString(7)));
                vo.setActivityAmount(Integer.valueOf(rs.getString(8)));
                vo.setDisplayAmount(Integer.valueOf(rs.getString(5)));
                vo.setSettlementAmount(Integer.valueOf(rs.getString(6)));
                list.add(vo);
            }
            con.close();
        } catch (Exception e) {
            logger.error("___" + e.getMessage());
        }


        return RecommendWeddingPlanner.getWorkerServiceList(list);
    }

    public static List<PlannerMonthVO> queryWorkerWeddingOrders(String wedding_date) {
        List<PlannerMonthVO> list = new ArrayList<>(200);
        try {
            Connection con = PhoenixConn.getConnection();
            Statement stmt = con.createStatement();
            StringBuilder sqlline = new StringBuilder();
            sqlline.append("select wo.worker_id, SUM( case  substr(wo.ctime,0,7) when   TO_CHAR(CONVERT_TZ(NOW(), 'UTC', 'Asia/Hong_Kong'), 'yyyy-MM')    then 1 else 0 end  ) thisOrderCount, ").append(
                    "SUM( case substr(wo.wedding_date,0,7) when   '").append(wedding_date.substring(0, 7)).append("'    then 1 else 0 end  ) thatOrderCount from v3_tb_worker_wedding_order wo inner join ").append(
                    "v3_tb_worker w on wo.worker_id = w.id where w.status = '0' and w.vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580' and " +
                            "w.daily_limit is not null and to_number( w.daily_limit) >0  and wo.is_use = '1'  and wo.status !='2' and wo.flag != '1' group by wo.worker_id");
//            String sql2 = "select id,avator,msisdn from v3_tb_worker_wedding_order";
//            logger.info("____ query sql queryWorkerWeddingOrders is " + sqlline.toString());
            ResultSet rs = stmt.executeQuery(sqlline.toString());
            while (rs.next()) {
                PlannerMonthVO vo = new PlannerMonthVO();
                vo.setWorkerId(rs.getString(1));
                vo.setThisOrderCount(rs.getInt(2));
                vo.setThatOrderCount(rs.getInt(3));
                list.add(vo);
            }
            con.close();
        } catch (Exception e) {
            logger.error("___" + e.getMessage());
        }
        return list;
    }


    public static List<PlannerScheduleVO> queryWorkerSchedule(String wedding_date) {
        List<PlannerScheduleVO> list = new ArrayList<>(200);
        try {
            Connection con = PhoenixConn.getConnection();
            Statement stmt = con.createStatement();
            StringBuilder sqlline = new StringBuilder();
            //select * from (
            sqlline.append(" select  worker_id,").append(
                    "sum(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd HH','GMT+13'),'yyyy-MM-dd') = schedule_date and type != '1' and type != '2' then 1 else 0 end  ) preday,").append(
                    "sum(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd HH','GMT-13'),'yyyy-MM-dd') = schedule_date and type != '1' and type != '2' then 1 else 0 end  ) postday,").append(
                    "sum(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM','GMT'),'yyyy-MM') = substr(schedule_date,0,7) and type != '1' and type != '2' then 1 else 0 end  ) monthCount,").append(
                    "case when  max(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd','GMT'),'yyyy-MM-dd') = schedule_date and (type = '1' or type = '2' ) then 1 else 0 end  ) > 0 then 0 else 1 end  daySchedule,").append(
                    " min(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd','GMT'),'yyyy-MM-dd') = schedule_date then ").append(
                    "case when type = '1'  then 3 when type = '2' then 4 else 5 end  else 5 end  )   canOrderType,").append(
                    " sum( case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd','GMT'),'yyyy-MM-dd') = schedule_date and type != '1' and type !='2'  then 1 else 0 end )   num").append(
                    " from v3_tb_worker_schedule ").append(" where ( substr(schedule_date,0,7) =  TO_CHAR(TO_DATE('").append(wedding_date).
                    append("','yyyy-MM-dd') - 1, 'yyyy-MM') or  substr(schedule_date,0,7) =  TO_CHAR(TO_DATE('").append(wedding_date).
                    append("','yyyy-MM-dd') + 1, 'yyyy-MM')").append(" ) and flag != '1' and is_use ='1' group by worker_id ");
            //) res where daySchedule > 0
//            logger.info("____ query sql queryWorkerSchedule is " + sqlline.toString());
            ResultSet rs = stmt.executeQuery(sqlline.toString());
            while (rs.next()) {
                PlannerScheduleVO vo = new PlannerScheduleVO();
                vo.setWorkerId(rs.getString(1));
                vo.setPreday(rs.getInt(2));
                vo.setPostday(rs.getInt(3));
                vo.setMonthCount(rs.getInt(4));
                vo.setDaySchedule(rs.getInt(5));
                vo.setCanOrderType(rs.getInt(6));
                vo.setNum(rs.getInt(7));
                list.add(vo);
            }
            con.close();
        } catch (Exception e) {
            logger.error("___" + e.getMessage());
        }
        return list;
    }


    public static List<PlannerLimitVO> queryWorkerDayLimit(String wedding_date) {
        List<PlannerLimitVO> list = new ArrayList<>(200);
        try {
            Connection con = PhoenixConn.getConnection();
            Statement stmt = con.createStatement();
            StringBuilder sqlline = new StringBuilder();
            sqlline.append("select lr.worker_id, min(case when lr.datatype = 1 then to_number(lr.receipt_num) end)  rn ,").append(
                    "min(case when lr.datatype = 2 then to_number(lr.limit_num) end)  ln ").append(
                    "from (").append(
                    "select ol0.worker_id,ol0.limit_num,ol0.receipt_num ,ol1.datatype from  v3_tb_worker_month_order_limit ol0 inner join (").append(
                    "select ol00.worker_id,ol00.year, ol00.month,max(ol00.utime) utime ,1 datatype ").append(
                    "from v3_tb_worker_month_order_limit ol00 inner join ").append(
                    "v3_tb_worker w on ol00.worker_id = w.id where w.status = '0' and w.vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580' and ").
                    append(" ol00.is_use = '1' and   ").
                    append("w.daily_limit is not null and to_number( w.daily_limit) >0 and  ol00.year = TO_CHAR(CURRENT_DATE(),'yyyy') and ol00.month = TO_CHAR(to_number( TO_CHAR(CURRENT_DATE(),'MM')))").append(
                    "group by ol00.worker_id ,ol00.year, ol00.month ").append(
                    "union all ").append(
                    "select ol01.worker_id,ol01.year, ol01.month,max(ol01.utime) utime ,2 datatype ").append(
                    "from v3_tb_worker_month_order_limit ol01 inner join  ").append(
                    "v3_tb_worker w on ol01.worker_id = w.id where w.status = '0' and w.vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580' and ").
                    append(" ol01.is_use = '1' and ").
                    append("w.daily_limit is not null and to_number( w.daily_limit) >0 and  ol01.year =  '").
                    append(wedding_date.substring(0, 4)).append("' and ol01.month = TO_CHAR(to_number('")
                    .append(wedding_date.substring(5, 7)).append("'))").append(
                    "group by ol01.worker_id ,ol01.year, ol01.month   ) ol1  on  ol0.worker_id = ol1.worker_id and ol0.year = ol1.year and ol0.month = ol1.month and ol0.utime = ol1.utime").
                    append(" where ol0.is_use = '1' ").
                    append(") lr group by lr.worker_id ");
//            logger.info("____ query sql queryWorkerDayLimit is " + sqlline.toString());
            ResultSet rs = stmt.executeQuery(sqlline.toString());
            while (rs.next()) {
                PlannerLimitVO vo = new PlannerLimitVO();
                vo.setPlannerId(rs.getString(1));
                vo.setLimitNum(rs.getInt(3));
                vo.setReceiptNum(rs.getInt(2));
                list.add(vo);
            }
            con.close();
        } catch (Exception e) {
            logger.error("___" + e.getMessage());
        }
        return list;
    }

    public static List<WorkerQueryInfoVO> queryCanOrderPlanner(String wedding_date, Integer flag) {
        logger.info("_____ v2 query schedule wedding_date " + wedding_date);
        long t1 = System.currentTimeMillis();
        List<WorkerQueryInfoVO> workersList = new ArrayList<>(200);

        List<PlannerUserVO> allWorker = queryAllWorkers();
        List<PlannerMonthVO> monthOrderList = queryWorkerWeddingOrders(wedding_date);

        Map<String, PlannerMonthVO> monthOrderMap = monthOrderList.stream()
                .collect(Collectors.toMap(PlannerMonthVO::getWorkerId, bs -> {
                    return bs;
                }));
        List<PlannerScheduleVO> scheduleList = queryWorkerSchedule(wedding_date);
        Map<String, PlannerScheduleVO> scheduleMap = scheduleList.stream()
                .collect(Collectors.toMap(PlannerScheduleVO::getWorkerId, bs -> {
                    return bs;
                }));
        List<PlannerLimitVO> monthLimitList = queryWorkerDayLimit(wedding_date);
        Map<String, PlannerLimitVO> monthLimitMap = monthLimitList.stream()
                .collect(Collectors.toMap(PlannerLimitVO::getPlannerId, bs -> {
                    return bs;
                }));
        List<WorkerServiceVO> serviceVOList = queryWorkerService();
        Map<String, List<WorkerServiceVO>> serviceMap = serviceVOList.stream()
                .collect(Collectors.groupingBy(WorkerServiceVO::getWorkerId));


        for (PlannerUserVO plannerVo : allWorker) {
            String plannerId = plannerVo.getId();
            if (!serviceMap.containsKey(plannerId)) continue;
            int dailyLimit = plannerVo.getDailyLimit();
            int daySchedule = dailyLimit;
            int canOrderType = 5;
            int monthCanOrder = 6;
            if (scheduleMap.containsKey(plannerId)) {
                PlannerScheduleVO scheduleVO = scheduleMap.get(plannerId);
                if (scheduleVO.getDaySchedule() > 0) {
                    daySchedule = scheduleVO.getNum() >= dailyLimit ? 0 : dailyLimit - scheduleVO.getNum();
                    canOrderType = scheduleVO.getNum() >= dailyLimit ? 1 : 5;
                } else {
                    daySchedule = 0;
                    canOrderType = scheduleVO.getCanOrderType();
                }
            }

            if (monthLimitMap.containsKey(plannerId)) {
                PlannerLimitVO limitVO = monthLimitMap.get(plannerId);
                // &&
                if (0 == limitVO.getLimitNum() || (monthOrderMap.containsKey(plannerId) &&
                        monthOrderMap.get(plannerId).getThatOrderCount() >= limitVO.getLimitNum())) {
                    daySchedule = 0;
                    canOrderType = 3;
                }
                // &&
                if ("b752fb9c-8d12-4f33-867f-baa1da2900e5".equals(plannerId)) {
                    System.out.println("sssss");
                }
                if (0 == limitVO.getReceiptNum() || (monthOrderMap.containsKey(plannerId) &&
                        monthOrderMap.get(plannerId).getThisOrderCount() >= limitVO.getReceiptNum())) {

                    daySchedule = 0;
                    canOrderType = 2;
                }
//                if (0 != limitVO.getReceiptNum()) {
                monthCanOrder = limitVO.getReceiptNum();
                if (monthOrderMap.containsKey(plannerId)) {
                    monthCanOrder = monthCanOrder >= monthOrderMap.get(plannerId).getThisOrderCount() ?
                            monthCanOrder - monthOrderMap.get(plannerId).getThisOrderCount() : 0;
                }
//                }
            }

            if ((flag == 1 && daySchedule > 0) || flag == 2) {
                WorkerQueryInfoVO queryInfoVO = new WorkerQueryInfoVO();
                BeanUtils.copyProperties(plannerVo, queryInfoVO);
                queryInfoVO.setCanOrderType(canOrderType);
                queryInfoVO.setSchedule(daySchedule);
                queryInfoVO.setMonthCanOrder(monthCanOrder);
                queryInfoVO.setServiceList(serviceMap.get(plannerId));
                queryInfoVO.setMonthOrdered(monthOrderMap.containsKey(plannerId) ? monthOrderMap.get(plannerId).getThatOrderCount() : 0);
                queryInfoVO.setYesterdayOrder(scheduleMap.containsKey(plannerId) ? scheduleMap.get(plannerId).getPreday() : 0);
                queryInfoVO.setTomorrowOrder(scheduleMap.containsKey(plannerId) ? scheduleMap.get(plannerId).getPostday() : 0);
                queryInfoVO.setCanOrder(daySchedule);
                queryInfoVO.setOrders(scheduleMap.containsKey(plannerId) ? scheduleMap.get(plannerId).getNum() : 0);
                workersList.add(queryInfoVO);
            }
        }

        logger.info("____ queryCanOrderPlanner speeds t1 time :" + (System.currentTimeMillis() - t1));
        workersList = RecommendWeddingPlanner.getWorkerVoList(workersList);
        workersList = RecommendWeddingPlanner.getUserVOListFillList(workersList);
        workersList = RecommendWeddingPlanner.getWorkerLocationList(workersList);
        logger.info("____ queryCanOrderPlanner speeds t2 time :" + (System.currentTimeMillis() - t1));

        return workersList;
    }


    public static List<WorkerVO> queryHasSchedulePlaner(String wedding_date) {
        StringBuilder sqlline = new StringBuilder();
        sqlline.append("select * from ( ").
                append("select msg.id,msg.name,u.avator,u.msisdn,msg.preday,msg.postday,msg.monthCount, ").append(
                "case when msg.rn is not null and msg.thisOrderCount is not null and msg.rn <= msg.thisOrderCount then 2  ").append(
                "when msg.ln is not null and msg.thatOrderCount is not null and msg.ln <= msg.thatOrderCount then 3 ").append(
                "when msg.num is  not null and msg.num >= msg.daily_limit then 1 else 5 end  canOrderType2, ").append(
                "case when msg.rn is not null and msg.thisOrderCount is not null and msg.rn <= msg.thisOrderCount then 0 ").append(
                "when msg.ln is not null and msg.thatOrderCount is not null and msg.ln <= msg.thatOrderCount then 0 ").append(
                "when msg.num is  not null and msg.num >= msg.daily_limit then 0 ").append(
                "when msg.num is null then msg.daily_limit else msg.daily_limit - msg.num end daySchedule2,").append(
                "  case when msg.rn is not null and msg.thisOrderCount is not null  then msg.rn - msg.thisOrderCount ").append(
                "  when msg.rn  is not null  and msg.thisOrderCount = null then msg.rn ").append(
                "  when msg.rn = null and msg.thisOrderCount is not null then 6 - msg.thisOrderCount ").append(
                " else 6 end monthCanOrder ").append(
                " from ( ").append(
                "select w.id,w.name,to_number(w.daily_limit) daily_limit ,u.avator,u.msisdn ,").append(
                "o.thisOrderCount,o.thatOrderCount,").append(
                "s.preday,s.postday,s.monthCount,s.daySchedule,s.canOrderType,s.num,").append(
                "olimit.ln ,olimit.rn ").append(
                "from v3_tb_worker w inner  join v3_tb_user u on w.user_id = u.id  ").append(
                "left join ( ").append(
                "select wo.worker_id, SUM( case substr(wo.ctime,0,7) when   TO_CHAR(CONVERT_TZ(NOW(), 'UTC', 'Asia/Hong_Kong'), 'yyyy-MM')    then 1 else 0 end  ) thisOrderCount, ").append(
                "SUM( case substr(wo.wedding_date,0,7) when   '").append(wedding_date.substring(0, 7))
                .append("'    then 1 else 0 end  ) thatOrderCount from v3_tb_worker_wedding_order wo inner join  ").append(
                "v3_tb_worker w on wo.worker_id = w.id where w.status = '0' and w.vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580' and ").append(
                "w.daily_limit is not null and to_number( w.daily_limit) >0 group by worker_id ").append(
                ") o  on w.id = o.worker_id ").append(
                "left join ( ").append(
                "select  worker_id,").append(
                "sum(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd HH','GMT).append(13'),'yyyy-MM-dd') = schedule_date and type != '1' and type != '2' then 1 else 0 end  ) preday,").append(
                "sum(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd HH','GMT-13'),'yyyy-MM-dd') = schedule_date and type != '1' and type != '2' then 1 else 0 end  ) postday,").append(
                "sum(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM','GMT'),'yyyy-MM') = substr(schedule_date,0,7) and type != '1' and type != '2' then 1 else 0 end  ) monthCount,").append(
                "case when  max(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd','GMT'),'yyyy-MM-dd') = schedule_date and (type = '1' or type = '2' ) then 1 else 0 end  ) = 1 then 0 else 1 end  daySchedule,").append(
                " min(case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd','GMT'),'yyyy-MM-dd') = schedule_date then ").append(
                "case when type = '1'  then 3 when type = '2' then 4 else 5 end  else 5 end  )   canOrderType,").append(
                " sum( case when TO_CHAR(TO_DATE('").append(wedding_date).append(" 12','yyyy-MM-dd','GMT'),'yyyy-MM-dd') = schedule_date and type != '1' and type !='2'  then 1 else 0 end )   num").append(
                " from v3_tb_worker_schedule ").append(" where substr(schedule_date,0,7) >=  TO_CHAR(TO_DATE('").append(wedding_date).
                append("','yyyy-MM-dd') - 1, 'yyyy-MM') and  substr(schedule_date,0,7) <=  TO_CHAR(TO_DATE('").append(wedding_date).
                append("','yyyy-MM-dd') + 1, 'yyyy-MM')").append(
                "  group by worker_id ) s  on w.id = s.worker_id ").append(
                "left join (").append(
                "select lr.worker_id, min(case when lr.datatype = 1 then to_number(lr.limit_num) end)  ln ,").append(
                "min(case when lr.datatype = 2 then to_number(lr.receipt_num) end)  rn ").append(
                "from (").append(
                "select ol0.worker_id,ol0.limit_num,ol0.receipt_num ,ol1.datatype from  v3_tb_worker_month_order_limit ol0 inner join (").append(
                "select ol00.worker_id,ol00.year, ol00.month,max(ol00.utime) utime ,1 datatype ").append(
                "from v3_tb_worker_month_order_limit ol00 inner join ").append(
                "v3_tb_worker w on ol00.worker_id = w.id where w.status = '0' and w.vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580' and ").append(
                "w.daily_limit is not null and to_number( w.daily_limit) >0 and  ol00.year = TO_CHAR(CURRENT_DATE(),'yyyy') and ol00.month = TO_CHAR(to_number( TO_CHAR(CURRENT_DATE(),'MM')))").append(
                "group by ol00.worker_id ,ol00.year, ol00.month ").append(
                "union all ").append(
                "select ol01.worker_id,ol01.year, ol01.month,max(ol01.utime) utime ,2 datatype ").append(
                "from v3_tb_worker_month_order_limit ol01 inner join  ").append(
                "v3_tb_worker w on ol01.worker_id = w.id where w.status = '0' and w.vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580' and ").append(
                "w.daily_limit is not null and to_number( w.daily_limit) >0 and  ol01.year =  '").append(wedding_date.substring(0, 4)).append("' and ol01.month = TO_CHAR(to_number('")
                .append(wedding_date.substring(5, 7)).append("'))").append(
                "group by ol01.worker_id ,ol01.year, ol01.month   ) ol1  on  ol0.worker_id = ol1.worker_id and ol0.year = ol1.year and ol0.month = ol1.month and ol0.utime = ol1.utime").append(
                ") lr group by lr.worker_id ").append(
                ") olimit  on w.id = olimit.worker_id").append(
                " where w.status = '0' and w.vocation_id = 'd677824c-bae7-11e7-99f0-408d5c0cd580' and s.daySchedule = 1 ").append(
                "and w.daily_limit is not null and to_number( w.daily_limit) >0 ").append(") msg  )  where daySchedule2> 0");

        logger.info("__ execute sql is " + sqlline.toString());

        List<WorkerVO> list = new ArrayList<>(200);
        try {
            Connection con = PhoenixConn.getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sqlline.toString());
            while (rs.next()) {
                WorkerVO vo = new WorkerVO();
//                vo.setId(rs.getString("id"));
//                vo.setName(rs.getString("name"));
//                vo.setUserId(rs.getString("avator"));

                vo.setId(rs.getString(1));
                vo.setName(rs.getString(2));
                vo.setAvator(rs.getString(3));
                list.add(vo);
            }
            con.close();
        } catch (Exception e) {
            logger.error("___" + e.getMessage());
        }
        return list;


    }

    public static void main(String[] args) {
//        queryHasSchedulePlaner("2020-11-13");
//        queryWorkerSchedule("2020-11-13");
        queryWorkerDayLimit("2020-11-13");

    }

}
